﻿using System;
using System.Data;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text.RegularExpressions;

namespace HsTec.Mobile.Green.Util
{
    /// <summary>
    /// 用于处理二期报表导入功能
    /// </summary>
    public class ReportHelper
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public ReportHelper()
        {

        }

        #region 成员变量
        //访问EXCEL路径
        private string _excelPath = "";
        //访问EXCEL工作表
        private string _sheetName = "";
        //存入数据库数据表
        private string _tableName = "";
        //数据库表中包含字段集合
        private Dictionary<string, string> _tableField = new Dictionary<string, string>();
        //报表生成日期
        private string _reportDate = "";
        //数据库表中包含字段数
        private int _tableFieldCount = 0;
        //用以存放区域名称与流水号
        private Dictionary<string, int> areaNameList = new Dictionary<string, int>();
        //用于存放区域编号和流水号
        private Dictionary<int, int> areaNoList = new Dictionary<int, int>();

        public string excelPath
        {
            set { _excelPath = value; }
            get { return _excelPath; }
        }

        public string sheetName
        {
            set { _sheetName = value; }
            get { return _sheetName; }
        }

        public string tableName
        {
            set { _tableName = value; }
            get { return _tableName; }
        }
        #endregion

        /// <summary>
        /// 根据上传的EXCEL文件 将数据库导入到表中
        /// </summary>
        /// <param name="sDate">数据日期</param>
        /// <param name="sType">文件的格式</param>
        /// <param name="iEditer">编辑人</param>
        /// <param name="dicRtn">返回错误结果</param>
        /// <returns>返回操作的情况</returns>
        public string importReport(string sDate, string sType, int iEditer, ref Dictionary<string, string> dicRtn)
        {
            //1.初始化
            dicRtn.Add("NoSheet", String.Empty);
            dicRtn.Add("Err", String.Empty);
            dicRtn.Add("Rept", String.Empty);
            initImportExcel();
            //2.导入EXCEL文件进入数据集
            DataSet excelDataSet = ExecelDataSet();
            if (excelDataSet == null)
            {
                return "请将导入EXCEL文件中多余的空白行和列删除，再导入！";
            }
            string strBack = String.Empty;
            //3.判断导入的EXCEL类型 0:规则型 1:非规则型
            if (sType == "1")
                strBack = importRuleReport(excelDataSet, sDate, iEditer, ref dicRtn);
            else
                strBack = importIrregularReport(excelDataSet, sDate, iEditer, ref dicRtn);
            return strBack;
        }

        #region 私有成员
        /// <summary>
        /// 导入非规则表格
        /// </summary>
        /// <param name="excelDataSet">包含多个表格的结果集</param>
        /// <param name="sDate">年月</param>
        /// <param name="iEditer">编辑人</param>
        private string importIrregularReport(DataSet excelDataSet, string sDate, int iEditer, ref Dictionary<string, string> dicRtn)
        {
            string rtn = String.Empty;
            //0.检查该表下是否包含多个数据表 如果是多个需要单独处理
            DataView mydv = SQLHelper.ExecuteDataView("select RT_ID,FieldName from tb_ReportTemplate where FieldParent = " + this._tableName + " and FieldChildCount>0");
            if (mydv.Count == 0)
            {
                string pi = SQLHelper.ExecuteString("select FieldSelect from tb_ReportTemplate where RT_ID='" + this._tableName + "'");
                if (pi == "1")
                {
                    string years = sDate.Substring(0, 4);
                    switch ((int.Parse(sDate.Substring(4, 2)) % 12) / 3)
                    {
                        case 0:
                            for (int i = 0; i <= 2; i++)
                            {
                                if (i != 0)
                                {
                                    sDate = years + "0" + i;
                                }
                                else
                                {
                                    sDate = DateTime.Parse(years + "-01-01").AddYears(-1).ToString("yyyy");
                                    sDate = sDate + "12";
                                }
                                rtn = IrregularReport(excelDataSet, sDate, iEditer, this._tableName, ref dicRtn);
                            }
                            break;
                        case 1:
                            for (int i = 3; i <= 5; i++)
                            {
                                sDate = years + "0" + i;
                                rtn = IrregularReport(excelDataSet, sDate, iEditer, this._tableName, ref dicRtn);
                            }
                            break;
                        case 2:
                            for (int i = 6; i <= 8; i++)
                            {
                                sDate = years + "0" + i;
                                rtn = IrregularReport(excelDataSet, sDate, iEditer, this._tableName, ref dicRtn);
                            }
                            break;
                        case 3:
                            for (int i = 9; i <= 11; i++)
                            {
                                if (i == 9)
                                    sDate = years + "0" + i;
                                else
                                    sDate = years + i;
                                rtn = IrregularReport(excelDataSet, sDate, iEditer, this._tableName, ref dicRtn);
                            }
                            break;
                    }

                }
                else
                {
                    rtn = IrregularReport(excelDataSet, sDate, iEditer, this._tableName, ref dicRtn);
                }
            }
            else
            {
                for (int i = 0; i < mydv.Count; i++)
                {
                    rtn += IrregularReport(excelDataSet, sDate, iEditer, mydv[i]["RT_ID"].ToString(), ref dicRtn);
                }
            }
            return rtn;
        }
        private string IrregularReport(DataSet excelDataSet, string sDate, int iEditer, string sTableName, ref Dictionary<string, string> dicRtn)
        {
            int col = 0;
            int row = 0;
            //1 获取字段以及对应的单元格
            Dictionary<string, string> list = new Dictionary<string, string>();         //保存单元格和对应的字段名
            Dictionary<string, string> listType = new Dictionary<string, string>();     //保存单元格和对应的数据类型
            DataView mydv = SQLHelper.ExecuteDataView("select FieldName,EecelCell,FieldType from tb_ReportTemplate where FieldParent = " + sTableName + " and FieldChildCount=0");
            list.Clear();
            listType.Clear();
            for (int i = 0; i < mydv.Count; i++)
            {
                if (!String.IsNullOrEmpty(mydv[i]["EecelCell"].ToString()))
                {
                    try
                    {
                        list.Add(mydv[i]["EecelCell"].ToString(), mydv[i]["FieldName"].ToString());
                        listType.Add(mydv[i]["FieldName"].ToString(), mydv[i]["FieldType"].ToString());
                    }
                    catch
                    {
                        return "自定义数据表中包含有重复EXCEL单元格（" + mydv[i]["EecelCell"].ToString() + "，" + mydv[i]["FieldName"].ToString() + "）！";
                    }
                }
            }
            //读取EXCEL表
            Dictionary<string, string> insertlist = new Dictionary<string, string>();
            SQLHelper.myconn.Open();
            SqlCommand command = SQLHelper.myconn.CreateCommand();
            SqlTransaction transaction;
            transaction = SQLHelper.myconn.BeginTransaction();
            command.Connection = SQLHelper.myconn;
            command.Transaction = transaction;
            try
            {
                foreach (DataTable dt in excelDataSet.Tables)
                {
                    insertlist.Clear();
                    int areaId = 0;
                    //获取数据表名称
                    string sTable = SQLHelper.ExecuteString("select FieldName from tb_ReportTemplate where RT_ID='" + sTableName + "'", SQLHelper.myconn, transaction);
                    //获取SHEET表名中的编号 以便确定区域的编号 从而获得区域的名称
                    try
                    {
                        //---------------------------------修改在EXCEL表中获取地市信息的方法  2010-09-21 START---------------------------//
                        //注意 EXCEL 表格SHEET表的 命名格式 如下：TR03-HF-210V10 获取HF 来判断地市信息
                        //string sNo = dt.TableName.Split('-')[1];
                        string sNo = "0";
                        try
                        {
                            if (dt.TableName.IndexOf('(') > 0)
                                sNo = dt.TableName.Substring(dt.TableName.IndexOf('(') + 1, dt.TableName.IndexOf(')') - dt.TableName.IndexOf('(') - 1);
                            else
                                sNo = "20";
                        }
                        catch
                        { }
                        sNo = SQLHelper.ExecuteString("select ID from Area where AreaNo='" + sNo + "'", SQLHelper.myconn, transaction);
                        if (String.IsNullOrEmpty(sNo))
                        {
                            dicRtn["Err"] = "工作表（" + dt.TableName + "）对应的地市信息不存在！";
                            continue;
                        }
                        //---------------------------------修改在EXCEL表中获取地市信息的方法  2010-09-21 END-----------------------------//
                        areaId = int.Parse(sNo); //获取区域ID
                        //判断是否重复
                        //TODO:判断表格是否存在 如果不存在提醒用户去生成表格
                        string sRept = SQLHelper.ExecuteString("select count(*) from [rt_" + sTable + "] where Years='" + sDate + "' and Area_ID='" + areaId + "'", SQLHelper.myconn, transaction);
                        if (sRept != "0")
                        {
                            dicRtn["Rept"] = "工作表：" + dt.TableName + "；日期：" + dt.TableName + "对应的数据已经导入！";
                            continue;
                        }

                    }
                    catch
                    {
                        transaction.Rollback();
                        return dt.TableName + "SHEET表命名格式不对或无法对应到指定区域！";
                    }
                    //获取字段单元格对应的数值
                    foreach (string cell in list.Keys)
                    {
                        //处理单元格 如E4 转换为 4,4
                        string[] aryCell = GetNumberOrChar(cell);
                        col = int.Parse(aryCell[1]);
                        row = int.Parse(aryCell[0]);
                        insertlist.Add(list[cell].ToString(), dt.Rows[row - 2][col].ToString());
                    }
                    insertlist.Add("Area_ID", areaId.ToString());
                    StringBuilder sqlFiled = new StringBuilder();
                    StringBuilder sqlValue = new StringBuilder();
                    //插入数据库构造SQL语句
                    bool iFlag = false;
                    string sDataType = String.Empty;
                    //-------------------------2010.10.2 修改数据验证部分 START-----------------------------------//
                    foreach (string sField in insertlist.Keys)
                    {
                        if (sField == "Area_ID")
                        {
                            iFlag = true;
                        }
                        else
                        {
                            switch (listType[sField])
                            {
                                case "int":
                                    iFlag = ValidateHelper.IsNumber(insertlist[sField], 10);
                                    sDataType = "整数型";
                                    break;
                                case "decimal":
                                    iFlag = ValidateHelper.IsDecimal(insertlist[sField], 18, 2);
                                    sDataType = "数字型";
                                    break;
                                default:
                                    iFlag = true;
                                    break;
                            }
                        }
                        if (!iFlag)
                        {
                            dicRtn["Err"] = dt.TableName + "表中数据字段" + sField + "数据类型应该是" + sDataType + "！<br>";
                            break;
                        }
                        sqlFiled.Append("[" + sField + "],");
                        sqlValue.Append("'" + insertlist[sField] + "',");
                    }
                    if (!iFlag)
                        continue;
                    //-------------------------2010.10.2 修改数据验证部分 END-----------------------------------//
                    StringBuilder sql = new StringBuilder();
                    sql.Append("insert into [rt_" + sTable + "]( ");
                    sql.Append(sqlFiled.ToString());
                    sql.Append("[AddUser],[AddDate],[Years]");
                    sql.Append(" ) VALUES ( ");
                    sql.Append(sqlValue.ToString());
                    sql.Append("'" + iEditer + "','" + DateTime.Now + "','" + sDate + "'");
                    sql.Append(")");
                    command.CommandText = sql.ToString();
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
                return "导入成功！";
            }
            catch
            {
                transaction.Rollback();
                return "导入失败！";
            }
            finally
            {
                transaction.Dispose();
                SQLHelper.myconn.Close();
            }
        }
        /// <summary>
        /// 导入规则表格
        /// </summary>
        /// <param name="excelDataSet">包含多个表格的结果集</param>
        /// <param name="sDate">年月</param>
        /// <param name="iEditer">编辑人</param>
        private string importRuleReport(DataSet excelDataSet, string sDate, int iEditer, ref Dictionary<string, string> dicRtn)
        {
            Dictionary<string, List<string[]>> insertInfo = new Dictionary<string, List<string[]>>();
            //获取数据表中包含的SHEET表名
            Dictionary<string, string> dicTable = new Dictionary<string, string>();
            dicTable = this.getTable(this._tableName);
            //定义一些变量
            List<string> colList = new List<string>();
            //区域流水号
            int areaId = 0;
            //获取EXCEL中所有的数据 并保存到LIST集合中
            foreach (DataTable dt in excelDataSet.Tables)
            {
                List<string[]> list = new List<string[]>();
                colList.Clear();
                //判断是否存在指定字表的EXCEL对应工作表 即 数据库中表名和EXCEL工作表表名要一致
                if (!dicTable.ContainsKey(dt.TableName.Replace("$", "").Trim('\'')))
                {
                    //如果不存在 将未导入的数据表 存入返回错误字典dicRtn中
                    dicRtn["NoSheet"] += dt.TableName.Replace("$", "").Trim('\'') + "不存在,";
                    continue;
                }
                //------------------------2010.8.24 修改关于重复数据录入的问题-----------------------------//
                //判断表中是否重复导入数据
                string sRepeatTable = dt.TableName.Replace("$", "").Trim('\'');
                string sRtnCount = SQLHelper.ExecuteString("select count(*) from [rt_" + dicTable[sRepeatTable] + "] where Years='" + sDate + "'");
                if (sRtnCount != "0")
                {
                    dicRtn["Rept"] += sRepeatTable + ",";
                    continue;
                }
                //------------------------2010.8.24 修改关于重复数据录入的问题-----------------------------//
                //获取列名 即所有地区名称
                for (int i = 1; i < dt.Columns.Count; i++)
                {
                    colList.Add(dt.Columns[i].ColumnName);
                }
                if (dt.Rows.Count != 1)
                    return "对于规则表格的导入，数据表只能包含一个月的数据信息！";
                //获取行值
                foreach (DataRow dr in dt.Rows)
                {
                    foreach (string sFieldName in colList)
                    {
                        //定义一个一维数组 用于存储 区域ID 字段值
                        string[] tmp = new string[] { "", "" };
                        try
                        {
                            areaId = areaNameList[sFieldName.Replace("分公司", "")];
                        }
                        catch
                        {
                            return sFieldName + "在地区数据表中不存在，请进去基础数据页面增加！";
                        }
                        tmp[0] = areaId.ToString();
                        tmp[1] = dr[sFieldName].ToString();
                        list.Add(tmp);
                    }
                }
                insertInfo.Add(dicTable[dt.TableName.Replace("$", "").Trim('\'')].ToString(), list);
            }
            //需要插入数据的工作表
            string table = this.tableName;
            //保存LIST集合中的值到数据库
            SQLHelper.myconn.Open();
            SqlCommand command = SQLHelper.myconn.CreateCommand();
            SqlTransaction transaction;
            transaction = SQLHelper.myconn.BeginTransaction();
            command.Connection = SQLHelper.myconn;
            command.Transaction = transaction;
            try
            {
                if (insertInfo.Keys.Count == 0)
                {
                    string sRtn = String.Empty;
                    if (!String.IsNullOrEmpty(dicRtn["Rept"]))
                    {
                        string[] ary = dicRtn["Rept"].Split(',');
                        for (int i = 0; i < ary.Length - 1; i++)
                        {
                            sRtn += sRtn == "" ? ary[i] : "," + ary[i];
                        }
                    }
                    return "以下Sheet表：" + sRtn + "已经导入到数据库中，请勿重复导入！";
                }
                foreach (string stable in insertInfo.Keys)
                {
                    //构造SQL语句
                    string sql = this.getInsertsql(stable);
                    foreach (string[] field in insertInfo[stable])
                    {
                        double dec = 0.00;
                        try
                        {
                            if (!String.IsNullOrEmpty(field[1]))
                                dec = Double.Parse(field[1]);
                            else
                                dec = 0.0;
                        }
                        catch
                        {
                            transaction.Rollback();
                            return stable + "表中的" + field[0] + "地区数据格式不正确！";
                        }
                        string insertsql = sql + "VALUES('" + field[0] + "','" + sDate + "','" + iEditer + "','" + DateTime.Now + "','" + dec.ToString("0.00") + "')";
                        command.CommandText = insertsql;
                        command.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
                return "导入成功！";
            }
            catch
            {
                transaction.Rollback();
                return "导入失败！";
            }
            finally
            {
                transaction.Dispose();
                SQLHelper.myconn.Close();
            }
        }

        private string getInsertsql(string stable)
        {
            string tmp = "insert into [rt_" + stable + "] (";
            DataView mydv = SQLHelper.ExecuteDataView("select FieldName from tb_ReportTemplate where FieldParent =(select top 1 RT_ID from tb_ReportTemplate where FieldName='" + stable + "')");
            tmp += "[Area_ID],[Years],[AddUser],[AddDate]";
            for (int i = 0; i < mydv.Count; i++)
            {
                tmp += ",[" + mydv[i]["FieldName"] + "]";
            }
            tmp += ")";
            return tmp;
        }

        /// <summary>
        /// 获取该节点下的已经生成的数据表
        /// </summary>
        /// <param name="sid">父节点ID</param>
        /// <returns></returns>
        private Dictionary<string, string> getTable(string sid)
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            DataView mydv = SQLHelper.ExecuteDataView("select FieldName,FieldAlias from tb_ReportTemplate where FieldParent=" + sid + " and FieldChildCount>0");
            // 查询指定节点sid下的所有的字表数量
            if (mydv.Count > 0)
            {
                for (int i = 0; i < mydv.Count; i++)
                {
                    dic.Add(mydv[i]["FieldAlias"].ToString(), mydv[i]["FieldName"].ToString());
                }
            }
            else
            {
                mydv = SQLHelper.ExecuteDataView("select FieldName,FieldAlias from tb_ReportTemplate where RT_ID=" + sid);
                dic.Add(mydv[0]["FieldAlias"].ToString(), mydv[0]["FieldName"].ToString());
            }
            return dic;
        }
        /// <summary>
        /// 将EXCEL中所有的SHEET工作表导入到结果集
        /// </summary>
        /// <returns></returns>
        private DataSet ExecelDataSet()
        {
            //建立EXCEL文件链接
            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + Util.ServerPath + excelPath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                //读取EXCEL表中所有的sheet表填充到到DataSet中
                conn.Open();
                DataTable sheettable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                List<string> list = new List<string>();
                //将所有的SHEET表名添加到List集合中
                for (int i = 0; i < sheettable.Rows.Count; i++)
                {
                    list.Add(sheettable.Rows[i][2].ToString());
                }
                DataSet ds = new DataSet();
                foreach (string sheetName in list)
                {
                    OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
                    odda.Fill(ds, sheetName);
                }
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 初始化导入EXCEL文件
        /// </summary>
        private void initImportExcel()
        {
            areaNameList.Clear();
            areaNoList.Clear();
            //获取所有地市信息 并缓存起来 为以后使用
            DataView mydv = SQLHelper.ExecuteDataView("select [ID], [AreaNo], [Name] from Area where ChildNumber = 0 and AreaNo>=0");
            for (int i = 0; i < mydv.Count; i++)
            {
                areaNameList.Add(mydv[i][2].ToString().Replace("市", ""), int.Parse(mydv[i][0].ToString()));
                areaNoList.Add(int.Parse(mydv[i][1].ToString()), int.Parse(mydv[i][0].ToString()));
            }
        }

        /// <summary>
        /// 获取单元格
        /// </summary>
        /// <param name="str"></param>
        /// <param name="type">1 代表 获取数字 2代表获取字符</param>
        /// <returns></returns>
        public string[] GetNumberOrChar(string str)
        {
            string[] rtn = new string[] { "", "" };
            if (str != null && str != string.Empty)
            {
                rtn[0] = Regex.Replace(str, @"[^\d]", "");
                rtn[1] = (this.ToIndex(Regex.Replace(str, @"[^a-z A-Z]", ""))).ToString();
            }
            return rtn;
        }

        #region EXCEL 列号和行号转换
        private int ToIndex(string columnName)
        {
            int index = 0;
            char[] chars = columnName.ToUpper().ToCharArray();
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            return index - 1;
        }


        private string ToName(int index)
        {
            if (index < 0) { throw new Exception("invalid parameter"); }

            List<string> chars = new List<string>();
            do
            {
                if (chars.Count > 0) index--;
                chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
                index = (int)((index - index % 26) / 26);
            } while (index > 0);

            return String.Join(string.Empty, chars.ToArray());
        }
        #endregion

        #endregion

        public string importTemplate(string sTableName, string sAliasTableName, string sType, string sTypeInfo, string sTable, ref Dictionary<string, string> dicRtn)
        {
            if (sTypeInfo == "1")
            {
                //判断处理的类型
                if (sType == "1")         //规则性
                {
                    #region 新模板入库（规则）
                    string tmp = String.Empty;
                    //0.检查是否存在 rt_字段名 ，如有需删除
                    string tbExist = SQLHelper.ExecuteString("SELECT ISNULL(OBJECTPROPERTY(OBJECT_ID('rt_" + sTableName + "'),   'IsUserTable'),   0)");
                    if (tbExist == "1")
                    {
                        try
                        {
                            SQLHelper.ExecuteString("drop table rt_" + sTableName);
                        }
                        catch
                        { }
                    }
                    StringBuilder sSql = new StringBuilder();
                    sSql.Append("CREATE TABLE [rt_" + sTableName + "]");
                    sSql.Append("(");
                    sSql.Append("[ID] [int]  IDENTITY (1, 1) PRIMARY key  NOT NULL");
                    sSql.Append(",[Area_ID] [int] NULL");
                    sSql.Append(",[Years] [varchar](6) NULL");
                    sSql.Append(",[AddUser] [int]  NULL");
                    sSql.Append(",[AddDate] [datetime]  NULL");
                    sSql.Append(",[Value] [varchar](50)  NULL");
                    sSql.Append(")");

                    SQLHelper.myconn.Open();
                    SqlCommand command = SQLHelper.myconn.CreateCommand();
                    SqlTransaction transaction;
                    transaction = SQLHelper.myconn.BeginTransaction();
                    command.Connection = SQLHelper.myconn;
                    command.Transaction = transaction;
                    try
                    {
                        command.CommandText = sSql.ToString();
                        command.ExecuteNonQuery();
                        command.CommandText = "insert into tb_ReportTemplate(FieldName,FieldAlias,FieldType,FieldParent,FieldChildCount) values('" + sTableName + "','" + sAliasTableName + "','0','0','1')";
                        command.ExecuteNonQuery();
                        string Uid = SQLHelper.ExecuteString("select max(RT_ID) form tb_ReportTemplate");
                        command.CommandText = "insert into tb_ReportTemplate(FieldName,FieldAlias,FieldType,FieldParent,FieldChildCount,FieldTemplate) values('Value','数据值','1','" + Uid + "','0','" + Uid + "')";
                        command.ExecuteNonQuery();
                        transaction.Commit();
                        return "导入模板成功！";
                    }
                    catch
                    {
                        transaction.Rollback();
                        return "导入模板失败！";
                    }
                    finally
                    {
                        transaction.Dispose();
                        SQLHelper.myconn.Close();
                    }
                    #endregion
                }
                else                    //非规则性表格
                {
                    #region   //处理新模板（非规则）入库
                    //1.读取Excel文件
                    string tmp = String.Empty;
                    DataSet TemplateDs = ExecelDataSet();
                    if (TemplateDs.Tables.Count >= 0)
                    {
                        //2.获取所有字段信息
                        Dictionary<string, string[]> dicField = new Dictionary<string, string[]>();
                        DataView templateDv = TemplateDs.Tables[0].DefaultView;
                        for (int i = 0; i < templateDv.Count; i++)
                        {
                            for (int j = 0; j < templateDv.Table.Columns.Count; j++)
                            {
                                tmp = templateDv[i][j].ToString();
                                if (tmp.Split('$').Length == 6)
                                {
                                    string[] aryFields = tmp.Split('$');
                                    dicField.Add(aryFields[0].ToString(), new string[] { aryFields[1].ToString(), aryFields[2].ToString(), aryFields[3].ToString(), aryFields[4].ToString(), aryFields[5].ToString() });
                                }
                            }
                        }
                        //0.检查是否存在 rt_字段名 ，如有需删除
                        string tbExist = SQLHelper.ExecuteString("SELECT ISNULL(OBJECTPROPERTY(OBJECT_ID('rt_" + sTableName + "'),   'IsUserTable'),   0)");
                        if (tbExist == "1")
                        {
                            try
                            {
                                SQLHelper.ExecuteString("drop table rt_" + sTableName);
                            }
                            catch
                            { }
                        }
                        StringBuilder sSql = new StringBuilder();
                        sSql.Append("CREATE TABLE [rt_" + sTableName + "]");
                        sSql.Append("(");
                        sSql.Append("[ID] [int]  IDENTITY (1, 1) PRIMARY key  NOT NULL");
                        sSql.Append(",[Area_ID] [int] NULL");
                        sSql.Append(",[Years] [varchar](6) NULL");
                        sSql.Append(",[AddUser] [int]  NULL");
                        sSql.Append(",[AddDate] [datetime]  NULL");
                        foreach (string key in dicField.Keys)
                        {
                            sSql.Append(",[" + key + "] [varchar](50)  NULL");
                        }
                        sSql.Append(")");

                        SQLHelper.myconn.Open();
                        SqlCommand command = SQLHelper.myconn.CreateCommand();
                        SqlTransaction transaction;
                        transaction = SQLHelper.myconn.BeginTransaction();
                        command.Connection = SQLHelper.myconn;
                        command.Transaction = transaction;
                        try
                        {
                            command.CommandText = sSql.ToString();
                            command.ExecuteNonQuery();
                            command.CommandText = "insert into tb_ReportTemplate(FieldName,FieldAlias,FieldType,FieldParent,FieldChildCount) values('" + sTableName + "','" + sAliasTableName + "','0','0','" + dicField.Count + "')";
                            command.ExecuteNonQuery();
                            string Uid = SQLHelper.ExecuteString("select max(RT_ID) from tb_ReportTemplate", SQLHelper.myconn, transaction);
                            int iNum = 1;
                            foreach (string key in dicField.Keys)
                            {
                                string[] ary = dicField[key];
                                if (ary[1] == "C")
                                    tmp = "2";
                                else
                                    tmp = "1";
                                command.CommandText = "insert into tb_ReportTemplate(FieldName,FieldAlias,FieldType,FieldParent,FieldChildCount,EecelCell,FieldTemplate,Unit,StatType) values('" + key + "','" + ary[0] + "','" + tmp + "','" + Uid + "','0','" + ary[2] + "','" + Uid + "','" + ary[3] + "','" + ary[4] + "')";
                                command.ExecuteNonQuery();
                                string rtid = SQLHelper.ExecuteString("select max(RT_ID) from tb_ReportTemplate", SQLHelper.myconn, transaction);
                                if (sAliasTableName.IndexOf("月度") >= 0)
                                {
                                    command.CommandText = "insert into tb_TemplatePara(RT_ID,Excel,Ver,OrderNo) values('" + rtid + "','" + ary[2] + "','" + DateTime.Now.ToString("yyyyMM") + "','" + iNum + "')";
                                    command.ExecuteNonQuery();
                                }
                                iNum++;
                            }
                            transaction.Commit();
                            return "导入模板成功！";
                        }
                        catch
                        {
                            transaction.Rollback();
                            return "导入模板失败！";
                        }
                        finally
                        {
                            transaction.Dispose();
                            SQLHelper.myconn.Close();
                        }
                    }
                    else
                    {
                        return "EXCEL文件中没有有效的Sheet工作表";
                    }
                    #endregion
                }
            }
            else
            {
                #region 修改模板
                string tmp = String.Empty;
                DataSet TemplateDs = ExecelDataSet();
                if (TemplateDs.Tables.Count >= 0)
                {
                    //2.获取所有字段信息
                    Dictionary<string, string[]> dicField = new Dictionary<string, string[]>();
                    DataView templateDv = TemplateDs.Tables[0].DefaultView;
                    for (int i = 0; i < templateDv.Count; i++)
                    {
                        for (int j = 0; j < templateDv.Table.Columns.Count; j++)
                        {
                            tmp = templateDv[i][j].ToString();
                            if (tmp.Split('$').Length == 6)
                            {
                                string[] aryFields = tmp.Split('$');
                                dicField.Add(aryFields[0].ToString(), new string[] { aryFields[1].ToString(), aryFields[2].ToString(), aryFields[3].ToString(), aryFields[4].ToString(), aryFields[5].ToString() });
                            }
                        }
                    }
                    string tbExist = String.Empty;
                    string sName = SQLHelper.ExecuteString("select FieldAlias from tb_ReportTemplate where RT_ID='" + sTable + "'");
                    if (sName.IndexOf("月度") >= 0)
                    {
                        sTableName = "tb_ReportAnalysis";
                        tbExist = "1";
                    }
                    else
                    {
                        //0.检查是否存在 rt_字段名 ，如有需删除
                        sTableName = SQLHelper.ExecuteString("select FieldName from tb_ReportTemplate where RT_ID='" + sTable + "'");
                        tbExist = SQLHelper.ExecuteString("SELECT ISNULL(OBJECTPROPERTY(OBJECT_ID('rt_" + sTableName + "'),   'IsUserTable'),   0)");
                    }
                    if (tbExist == "1")
                    {
                        SQLHelper.myconn.Open();
                        SqlCommand command = SQLHelper.myconn.CreateCommand();
                        SqlTransaction transaction;
                        transaction = SQLHelper.myconn.BeginTransaction();
                        command.Connection = SQLHelper.myconn;
                        command.Transaction = transaction;
                        try
                        {
                            Dictionary<string, string[]> dicTableField = new Dictionary<string, string[]>();
                            //获取该表下的所有字段
                            DataView dvField = SQLHelper.ExecuteDataView("select RT_ID,FieldName,FieldAlias,FieldType,EecelCell from tb_ReportTemplate where FieldParent='" + sTable + "'", SQLHelper.myconn, transaction);
                            for (int i = 0; i < dvField.Count; i++)
                            {
                                dicTableField.Add(dvField[i]["FieldName"].ToString(), new string[] { dvField[i]["FieldAlias"].ToString(), dvField[i]["FieldType"].ToString(), dvField[i]["EecelCell"].ToString(), dvField[i]["RT_ID"].ToString() });
                            }
                            int iNum = 1;
                            foreach (string key in dicField.Keys)
                            {
                                if (dicTableField.ContainsKey(key))
                                {
                                    //修改tb_ReportTemplate表中的记录
                                    string[] ary = dicField[key];
                                    string[] arylist = dicTableField[key];
                                    if (sTableName == "tb_ReportAnalysis")
                                    {
                                        string tmpid = SQLHelper.ExecuteString("select TP_ID from tb_TemplatePara where RT_ID='" + arylist[3] + "' and Ver='" + DateTime.Now.ToString("yyyyMM") + "'", SQLHelper.myconn, transaction);
                                        if (String.IsNullOrEmpty(tmpid))
                                        {
                                            if (ary[1] == "C")
                                                tmp = "2";
                                            else
                                                tmp = "1";
                                            command.CommandText = "insert into tb_TemplatePara(RT_ID,Excel,Ver,OrderNo) values('" + arylist[3] + "','" + ary[2] + "','" + DateTime.Now.ToString("yyyyMM") + "','" + iNum + "')";
                                            command.ExecuteNonQuery();
                                        }
                                        else
                                        {
                                            command.CommandText = "update tb_TemplatePara set RT_ID='" + arylist[3] + "',Excel='" + ary[2] + "',OrderNo='" + iNum + "' where TP_ID='" + tmpid + "'";
                                            command.ExecuteNonQuery();
                                        }
                                    }

                                    command.CommandText = "update tb_ReportTemplate set EecelCell ='" + ary[2] + "',FieldAlias='" + ary[0] + "',Unit='" + ary[3] + "',StatType='" + ary[4] + "' where FieldParent='" + sTable + "' and FieldName='" + key + "'";
                                    command.ExecuteNonQuery();

                                }
                                else
                                {
                                    string[] ary = dicField[key];
                                    if (ary[1] == "N")
                                        tmp = "decimal(18,2)";
                                    else if (ary[1] == "N")
                                        tmp = "int";
                                    else
                                        tmp = "varchar(50)";
                                    //增加字段 数据类型
                                    if (sTableName == "tb_ReportAnalysis")
                                    {
                                        command.CommandText = " alter table [tb_ReportAnalysis] add [" + key + "] " + tmp + " NULL ";
                                        command.ExecuteNonQuery();
                                        command.CommandText = " alter table [tb_ReportAnalysisOnline] add [" + key + "] " + tmp + " NULL ";
                                        command.ExecuteNonQuery();
                                    }
                                    else
                                    {
                                        command.CommandText = " alter table [rt_" + sTableName + "] add [" + key + "] " + tmp + " NULL ";
                                        command.ExecuteNonQuery();
                                    }
                                    //修改tb_ReportTemplate表中的记录
                                    if (ary[1] == "C")
                                        tmp = "2";
                                    else
                                        tmp = "1";
                                    command.CommandText = "insert into tb_ReportTemplate(FieldName,FieldAlias,FieldType,FieldParent,FieldChildCount,EecelCell,Unit,StatType) values('" + key + "','" + ary[0] + "','" + tmp + "','" + sTable + "','0','" + ary[2] + "','" + ary[3] + "','" + ary[4] + "')";
                                    command.ExecuteNonQuery();
                                    string rtid = SQLHelper.ExecuteString("select max(RT_ID) from tb_ReportTemplate", SQLHelper.myconn, transaction);
                                    if (sTableName == "tb_ReportAnalysis")
                                    {
                                        command.CommandText = "insert into tb_TemplatePara(RT_ID,Excel,Ver,OrderNo) values('" + rtid + "','" + ary[2] + "','" + DateTime.Now.ToString("yyyyMM") + "','" + iNum + "')";
                                        command.ExecuteNonQuery();
                                    }
                                }
                                iNum++;
                            }
                            //分析字段
                            transaction.Commit();
                            return "导入模板成功！";
                        }
                        catch
                        {
                            transaction.Rollback();
                            return "导入模板失败！";
                        }
                        finally
                        {
                            transaction.Dispose();
                            SQLHelper.myconn.Close();
                        }
                    }
                    else
                    {
                        return "修改的数据表不存在，请新增后再修改！";
                    }
                }
                else
                {
                    return "EXCEL文件中没有有效的Sheet工作表";
                }

                #endregion
            }
        }

        public string importReport()
        {
            string tmp = String.Empty;
            DataSet TemplateDs = ExecelDataSet();
            if (TemplateDs.Tables.Count == 1)
            {
                //2.获取所有字段信息
                List<string>[] list = new List<string>[] { new List<string>(), new List<string>(), new List<string>(), new List<string>(), new List<string>(), new List<string>(), new List<string>() };

                DataView templateDv = TemplateDs.Tables[0].DefaultView;
                for (int i = 4; i < 113; i++)
                {
                    for (int j = 6, k = 0; j < templateDv.Table.Columns.Count - 2; j++, k++)
                    {
                        list[k % 7].Add(templateDv[i][j].ToString());
                    }
                }
                string mysql = " SELECT RTRIM(b.name) AS colname, CASE WHEN h.id IS NOT NULL " +
                           "       THEN 'PK' ELSE '' END AS primarykey, type_name(b.xusertype) " +
                           "       + CASE WHEN b.colstat & 1 = 1 THEN '[ID(' + CONVERT(varchar, ident_seed(a.name)) " +
                           "       + ',' + CONVERT(varchar, ident_incr(a.name)) + ')]' ELSE '' END AS type, b.length, " +
                           "       CASE b.isnullable WHEN 0 THEN 'N' ELSE 'Y' END AS isnull, ISNULL(e.text, '') " +
                           "       AS [default], ISNULL(c.[value], '') AS descript " +
                           " FROM (SELECT g.id, g.colid " +
                           "         FROM sysindexes f, sysindexkeys g " +
                           "         WHERE f.id = g.id AND f.indid = g.indid AND f.indid > 0 AND f.indid < 255 AND " +
                           "               (f.status & 2048) <> 0) h RIGHT OUTER JOIN " +
                           "       syscolumns b INNER JOIN " +
                           "       sysobjects a ON b.id = a.id LEFT OUTER JOIN " +
                           "       sys.extended_properties c ON b.id = c.major_id AND b.colid = c.minor_id LEFT OUTER JOIN " +
                           "       syscomments e ON b.cdefault = e.id ON h.id = b.id AND h.colid = b.colid " +
                           " WHERE (a.id = OBJECT_ID('tb_Plan')) AND b.name!='P_ID'" +
                           " ORDER BY b.colid";
                string fieldList = String.Empty;
                DataView dvField = SQLHelper.ExecuteDataView(mysql);
                for (int i = 0; i < dvField.Count; i++)
                {
                    if (dvField[i]["colname"].ToString() != "P_ID")
                        fieldList += String.IsNullOrEmpty(fieldList) ? dvField[i]["colname"].ToString() : "," + dvField[i]["colname"].ToString();
                }
                SQLHelper.myconn.Open();
                SqlCommand command = SQLHelper.myconn.CreateCommand();
                SqlTransaction transaction;
                transaction = SQLHelper.myconn.BeginTransaction();
                command.Connection = SQLHelper.myconn;
                command.Transaction = transaction;
                try
                {
                    int start = 2008;
                    string fieldValue = String.Empty;
                    for (int i = 0; i < 7; i++)
                    {
                        start++;
                        fieldValue = String.Empty;
                        string tmpValue = String.Empty;
                        foreach (string value in list[i])
                        {

                            try
                            {
                                tmpValue = double.Parse(value.Replace("%", "")).ToString("0.00");
                            }
                            catch
                            {
                                tmpValue = "0.00";
                            }
                            fieldValue += String.IsNullOrEmpty(fieldValue) ? "'" + tmpValue + "'" : ",'" + tmpValue + "'";
                        }
                        command.CommandText = "insert into tb_Plan(" + fieldList + ") values('0','" + start.ToString() + "'," + fieldValue + ")";
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    return "导入模板成功！";
                }
                catch
                {
                    transaction.Rollback();
                    return "导入模板失败！";
                }
                finally
                {
                    transaction.Dispose();
                    SQLHelper.myconn.Close();
                }
            }
            return null;
        }
    }
}
